﻿using SqlPerfomance.V2.DomainModel.Enums;
using SqlPerfomance.V2.DomainModel.Models;
using SqlPerfomance.V2.DomainModel.NodeTree;
using SqlPerfomance.V2.DomainModel.NodeTree.Interfaces;
using SqlPerfomance.V2.Helpers;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Data.SQLite;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace SqlPerfomance.V2.DAL
{
    public class MaterializedPathTreeRepository : RealDataRepository<MaterializedPathTreeNode>
	{
        private DatabaseLogger logger;
        public MaterializedPathTreeRepository()
        {
            logger = new DatabaseLogger();
        }
		#region Create MaterializedPathTreeNode
		private StringBuilder _mpTreePath = new StringBuilder();

        public override ITree<MaterializedPathTreeNode> CreateTree(INode<CommonTreeNode> commonTree)
        {
            ITree<MaterializedPathTreeNode> mpTree = NodeTree<MaterializedPathTreeNode>.NewTree();
            //mpTree.Inserted += MpTreeOnInserted;

            ITree<MaterializedPathTreeNode> nsRoot = mpTree;
            var mpTreeTopNode = new MaterializedPathTreeNode()
            {
                Id = 1,
                Name = "root",
                Path = GetPath(0),
                Level = 0
            };
            INode<MaterializedPathTreeNode> mpTop = nsRoot.AddChild(mpTreeTopNode);
            INode<CommonTreeNode> topNode = commonTree.Root.DirectChildren.Nodes.First();
            AddItem(mpTop, topNode);

            //mpTree.Inserted -= MpTreeOnInserted;
            return mpTree;
        }

		private void AddItem(INode<MaterializedPathTreeNode> mpNode, INode<CommonTreeNode> commonTree, string path = "0", int level = 0)
		{
			int addPath = 0;

			level++;
			foreach (var node in commonTree.DirectChildren.Nodes)
			{
				_mpTreePath = new StringBuilder();
				string childPath = path + "." + (++addPath);
				var child = new MaterializedPathTreeNode()
				{
					Id = node.Data.Id,
					Name = node.Data.Name,
					Path = childPath, //GetPathRecursive(mpNode) + GetPath(node.Data.Id), $path . '.' . (++$add_path);
					Level = node.Depth
				};
				var nsChild = mpNode.AddChild(child);
				AddItem(nsChild, node, childPath, level);
			}
		}

		private string GetPath(int id)
		{
			return id.ToString().ToUpper();
		}

		private string GetPathRecursive(INode<MaterializedPathTreeNode> mpTree)
		{
			if (!mpTree.IsRoot)
			{
				_mpTreePath.Insert(0, mpTree.Data.Id.ToString() + ".");
				mpTree = mpTree.Parent;
				GetPathRecursive(mpTree);
			}
			return _mpTreePath.ToString().ToUpper();
		}

		private void MpTreeOnInserted(object sender, NodeTreeInsertEventArgs<MaterializedPathTreeNode> nodeTreeInsertEventArgs)
		{
			NodeTreeSave(nodeTreeInsertEventArgs.Node, ConnectionStrings.SqlServerConnectionString);
			//NodeTreeSave(nodeTreeInsertEventArgs.Node, ConnectionStrings.MySqlConnectionString);
		}
		#endregion

		#region LoadMpTreeFromDb


		#region SqlQueries
		private const string MpTreeQuery = @"
SELECT * FROM {1} 
WHERE path LIKE
(
	SELECT printf('%s%', path) FROM {1} 
	WHERE Id = {0} LIMIT 1
) AND 
(
	SELECT LENGTH(path) FROM {1}
	WHERE Id = {0} LIMIT 1
) + 8 == LENGTH(path);";

		private const string MpTreeTopNodeSqlServerQuery = @"
DECLARE @len INT;
SELECT @len = MIN(LEN([path])) FROM {0}
SELECT {1} * FROM {0} WHERE LEN([path]) = @len";
		private const string MpTreeTopNodeMySqlQuery = @"
#DECLARE @len INT;
SET @len := (SELECT MIN(LENGTH(path)) FROM {0});
SELECT * FROM {0} WHERE LENGTH(path) = @len {1}";

		private const string Query = @"
	SELECT *
		FROM {0}";
		#endregion

		#region SqliteQueries

		private const string SqliteTableName = "MpTreeTable";

		private const string SqliteTableQuery =
			"CREATE TABLE {0}('Id' INT PRIMARY KEY, 'Name' TEXT, 'Path' TEXT);";
		private const string SqliteInsertQuery =
			"insert into {0}(id, Name, Path) values({1}, '{2}', '{3}');";

		private const string SqliteIndexQuery = @"CREATE INDEX `indx_MpTreeTable_Id` ON `{0}` (`Id` ASC);
CREATE INDEX `indx_MpTreeTable_Path` ON `{0}` (`Path` ASC);";
		#endregion

		private ITree<MaterializedPathTreeNode> MpTree { get; set; }

		public IEnumerable<MaterializedPathTreeExtensionNode> GetMaterializedPathData(ConnectionStrings connectionString, string tableName)
		{
			string query = string.Empty;
			switch (connectionString)
			{
				case ConnectionStrings.MySqlConnectionString:
					query = string.Format(@"SELECT *
											, 0 as parent_id
										FROM {0} mp
										LIMIT 1
										UNION ALL
										SELECT node.*
											,parent.id as parent_id
												FROM {0} node, {0} parent
												WHERE node.path LIKE CONCAT(parent.path, '%')
												AND node.level = parent.level + 1", tableName);
					break;
				case ConnectionStrings.SqlServerConnectionString:
					query = string.Format(@"SELECT TOP 1 *
												, 0 as [parent_id]
											FROM {0} mp
											UNION ALL
											SELECT node.*
												,parent.id as [parent_id]
													FROM {0} node, {0} parent
													WHERE node.path LIKE CONCAT(parent.path, '%')
													AND node.level = parent.level + 1", tableName);
					break;
			}
			using(var conn = GetDbConnection(connectionString))
			{
				conn.Open();
				using(var cmd = conn.CreateCommand())
				{
					cmd.CommandText = query;
					var reader = cmd.ExecuteReader();
					List<MaterializedPathTreeExtensionNode> nodes = new List<MaterializedPathTreeExtensionNode>();
					while(reader.Read())
					{
						nodes.Add(new MaterializedPathTreeExtensionNode
							{
								Id = reader.GetInt32(0),
								Name = reader.GetString(1),
								Path = reader.GetString(2),
								Level = reader.GetInt32(3),
								ParentId = reader.GetInt32(4)
							});
					}
					return nodes;
				}
			}
		}

		public ITree<MaterializedPathTreeNode> GetMaterializedPathTree(ConnectionStrings conStr)
		{
			LoadMpTree(conStr, GetNodeTreeTableName(typeof(MaterializedPathTreeNode)));
			var result = MpTree;
			MpTree = null;
			return result;

		}

		private void LoadMpTree(ConnectionStrings conStr, string tableName)
		{
			MpTree = NodeTree<MaterializedPathTreeNode>.NewTree();
			var topNode = MpTree.AddChild(GetTopNodeOfMpTree(conStr, tableName));

			var allMpNodes = LoadMpTreeFromSqlDatabase(conStr, tableName);

			//var connection = new SQLiteConnection("Data Source=:memory:;Version=3;New=True;");
			//connection.Open();

			var connection = GetDbConnection(conStr);
			connection.Open();
			//InsertNsTreeIntoSqliteDatabase(connection, allMpNodes);
			try
			{
				AddMpTreeChildNodesReal(connection, topNode);
			}
			catch (Exception ex)
			{
				string s = ex.Message;
			}
			connection.Close();
		}

		private void InsertNsTreeIntoSqliteDatabase(SQLiteConnection connection, IEnumerable<MaterializedPathTreeNode> allMpNodes)
		{
			using (var cmd = new SQLiteCommand(connection))
			{
				cmd.CommandText = string.Format(SqliteTableQuery, SqliteTableName);
				cmd.ExecuteNonQuery();
			}
			foreach (var node in allMpNodes)
			{
				using (var command = new SQLiteCommand(connection))
				{
					command.CommandText = string.Format(SqliteInsertQuery,
						SqliteTableName, node.Id, node.Name, node.Path);
					command.ExecuteNonQuery();
				}
			}
			using (var command = new SQLiteCommand(connection))
			{
				command.CommandText = string.Format(SqliteIndexQuery, SqliteTableName);
				command.ExecuteNonQuery();
			}
		}

		public IEnumerable<MaterializedPathTreeNode> LoadMpTreeFromSqlDatabase(ConnectionStrings conStr, string tableName)
		{
			var allMpNodes = new List<MaterializedPathTreeNode>();
			using (DbConnection conn = GetDbConnection(conStr))
			{
				conn.Open();
				using (DbCommand cmd = conn.CreateCommand())
				{
                    string sql =  string.Format(Query, tableName);
                    cmd.CommandText = sql;
                    logger.Log(conStr, sql, "MP_LoadTreeFromSqlDatabase");
					DbDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
					while (reader.Read())
					{
						var node = new MaterializedPathTreeNode
						{
							Id = (int)reader["Id"],
							Name = (string)reader["Name"],
							Path = (string)reader["Path"],
							Level = (int)reader["level"]
						};
						allMpNodes.Add(node);
					}
				}
			}
			return allMpNodes;
		}

		private void AddMpTreeChildNodesReal(DbConnection conn, INode<MaterializedPathTreeNode> tree)
		{
			var childrens = new List<MaterializedPathTreeNode>();
			using (var cmd = conn.CreateCommand())
			{
				cmd.CommandText = string.Format(MpTreeQuery, tree.Data.Id,
					"MpTreeTable");
				var reader = cmd.ExecuteReader();
				while (reader.Read())
				{
					var node = new MaterializedPathTreeNode()
					{
						Id = (int)reader["Id"],
						Name = (string)reader["Name"],
						Path = (string)reader["Path"],
						Level = (int)reader["level"]
					};
					childrens.Add(node);
				}
			}
			if (childrens.Count != 0)
			{
				Parallel.ForEach(childrens, child =>
				{
					var addedChild = tree.AddChild(child);
					AddMpTreeChildNodesReal(conn, addedChild);
				});

			}
		}

		private void AddMpTreeChildNodes(SQLiteConnection conn, INode<MaterializedPathTreeNode> tree)
		{
			var childrens = new List<MaterializedPathTreeNode>();
			using (SQLiteCommand cmd = conn.CreateCommand())
			{
				cmd.CommandText = string.Format(MpTreeQuery, tree.Data.Id,
					SqliteTableName);
				var reader = cmd.ExecuteReader();
				while (reader.Read())
				{
					var node = new MaterializedPathTreeNode()
					{
						Id = (int)reader["Id"],
						Name = (string)reader["Name"],
						Path = (string)reader["Path"],
						Level = (int)reader["level"]
					};
					childrens.Add(node);
				}
			}
			if (childrens.Count != 0)
			{
				Parallel.ForEach(childrens, child =>
				{
					var addedChild = tree.AddChild(child);
					AddMpTreeChildNodes(conn, addedChild);
				});

			}
		}

		private MaterializedPathTreeNode GetTopNodeOfMpTree(ConnectionStrings conStr, string tableName)
		{
			string query = string.Empty;
			switch (conStr)
			{
				case ConnectionStrings.MySqlConnectionString:
					query = string.Format(MpTreeTopNodeMySqlQuery, tableName, "LIMIT 1");
					break;
				case ConnectionStrings.SqlServerConnectionString:
					query = string.Format(MpTreeTopNodeSqlServerQuery, tableName, "TOP 1");
					break;
			}
			var mpTree = new MaterializedPathTreeNode();
			using (DbConnection conn = GetDbConnection(conStr))
			{
				conn.Open();
				using (DbCommand cmd = conn.CreateCommand())
				{
					cmd.CommandText = query;
					cmd.CommandTimeout = 360;
					DbDataReader reader = cmd.ExecuteReader();
					while (reader.Read())
					{
						mpTree.Id = (int)reader["Id"];
						mpTree.Path = (string)reader["Path"];
						mpTree.Name = (string)reader["Name"];
						mpTree.Level = (int)reader["level"];
					}
				}
			}
			return mpTree;
		}

		#endregion

		#region Tests

		private IEnumerable<MaterializedPathTreeNode> SelectNodes(string sql, ConnectionStrings connectionString)
		{
			using (var conn = GetDbConnection(connectionString))
			{
				conn.Open();
				using (var cmd = conn.CreateCommand())
				{
					cmd.CommandText = sql;
					var reader = cmd.ExecuteReader();
					List<MaterializedPathTreeNode> nodes = new List<MaterializedPathTreeNode>();
					while (reader.Read())
					{
						nodes.Add(new MaterializedPathTreeNode
						{
							Id = (int)reader["id"],
							Name = (string)reader["name"],
							Path = (string)reader["path"],
							Level = (int)reader["level"]
						});
					}
					return nodes;
				}
			}
		}

		public IEnumerable<MaterializedPathTreeNode> SelectPathById(ConnectionStrings connectionString, string tableName, int id)
		{
			string sql = string.Format(@"SELECT parent.id
												,parent.name
												,parent.path
												,parent.level
													FROM {0} parent, {0} node
													WHERE node.path LIKE CONCAT(parent.path, '%')
													AND node.id = {1}", tableName, id);
            logger.Log(connectionString, sql, "MP_SelectPathById");
			return SelectNodes(sql, connectionString);
		}

		public IEnumerable<MaterializedPathTreeNode> SelectBranchById(ConnectionStrings connectionString, string tableName, int id)
		{

			string sql = string.Format(@"SELECT node.id
													,node.name
													,node.path
													,node.level
												FROM {0} node, {0} parent
												WHERE node.path LIKE CONCAT(parent.path, '%')
												AND parent.id = {1}", tableName, id);
            logger.Log(connectionString, sql, "MP_SelectBranchById");
			return SelectNodes(sql, connectionString);
		}

		public IEnumerable<MaterializedPathTreeNode> SelectParentById(ConnectionStrings connectionString, string tableName, int id)
		{
            string sql = string.Empty;
            if(connectionString == ConnectionStrings.SqlServerConnectionString)
			    sql = string.Format(@"SELECT parent.id
												,parent.name
												,parent.path
												,parent.level
											FROM {0} node, {0} parent
											WHERE parent.path = SUBSTRING( node.path, 1, (LEN(node.path) - CHARINDEX('.', REVERSE(node.path))))
											AND node.id = {1}", tableName, id);
            else
                sql = string.Format(@"SELECT parent.id
												,parent.name
												,parent.path
												,parent.level
											FROM {0} node, {0} parent
											WHERE parent.path = SUBSTRING( node.path, 1, (LENGTH(node.path) - LOCATE('.', REVERSE(node.path))))
											AND node.id = {1}", tableName, id);
            logger.Log(connectionString, sql, "MP_SelectParentById");
			return SelectNodes(sql, connectionString);
		}

		public IEnumerable<MaterializedPathTreeNode> SelectChildrenById(ConnectionStrings connectionString, string tableName, int id)
		{
			string sql = string.Format(@"SELECT node.id
											,node.name
											,node.path
											,node.level
										FROM {0} node, {0} parent
										WHERE node.path LIKE CONCAT(parent.path, '%')
										AND node.level = parent.level + 1
										AND parent.id = {1}", tableName, id);
            logger.Log(connectionString, sql, "MP_SelectChildrenById");
			return SelectNodes(sql, connectionString);
		}

		public void AddChildrenNode(ConnectionStrings connectionString, string tableName, int idParent)
		{
			string randomName = RandomStrings.GetRandomString(10);
            string sql = string.Empty;
            if(connectionString == ConnectionStrings.SqlServerConnectionString)
			    sql = string.Format(@"
								DECLARE @parentId INT
								SET @parentId = {0}

								DECLARE @childPos INT
								DECLARE @nodePath VARCHAR(100)
								DECLARE @nodeLevel INT

								SELECT @childPos = count(node.id) + 1
										FROM {1} node, {1} parent
										WHERE node.path LIKE CONCAT(parent.path, '%') AND node.level = parent.level + 1 AND parent.id = @parentId


								SELECT @nodePath = CONCAT(parent.path, '.', @childPos), @nodeLevel = parent.level + 1
										FROM {1} node, {1} parent
										WHERE parent.path = SUBSTRING( node.path, 1, (LEN(node.path) - CHARINDEX('.', REVERSE(node.path)))) AND node.id = @parentId

								INSERT INTO MpTreeTable VALUES('{2}', @nodePath, @nodeLevel)", idParent, tableName, randomName);
            else
            {
                sql = string.Format(@"SET @parentId := {0};
		                        SELECT @childPos := count(node.id) + 1
				                        FROM {1} node, {1} parent
				                        WHERE node.path LIKE CONCAT(parent.path, '%') AND node.level = parent.level + 1 AND parent.id = @parentId; 
        
		                        SELECT @nodePath := CONCAT(parent.path, '.', @childPos), @nodeLevel := parent.level + 1
				                        FROM {1} node, {1} parent
				                        WHERE parent.path = SUBSTRING( node.path, 1, (LENGTH(node.path) - LOCATE('.', REVERSE(node.path)))) AND node.id = @parentId;
       
                               INSERT INTO MpTreeTable (name, path, level) VALUES('{2}', @nodePath, @nodeLevel);", idParent, tableName, randomName);

            }
            logger.Log(connectionString, sql, "MP_AddNode");
			ExecuteQuery(sql, connectionString);
		}

		public void MoveNode(ConnectionStrings connectionString, int nodeForMove, int parentNode)
		{
            string sql = string.Empty;
            if (connectionString == ConnectionStrings.SqlServerConnectionString)
            {
                sql = string.Format(@"EXEC [dbo].[MOVE_NODE_MP]
												@node_id = {0},
												@parent_id = {1}", nodeForMove, parentNode);
            }
            else
            {
                sql = string.Format(@"CALL MOVE_NODE_MP({0}, {1})", nodeForMove, parentNode);
            }
            logger.Log(connectionString, sql, "MP_MoveNode");
			ExecuteQuery(sql, connectionString);
		}

		public void RemoveNode(ConnectionStrings connectionString, int nodeForRemove)
		{
            string sql = string.Empty;
            if (connectionString == ConnectionStrings.SqlServerConnectionString)
            {
                sql = string.Format(@"EXEC [dbo].[REMOVE_NODE_MP]
												@node_id = {0}", nodeForRemove);
            }
            else 
            {
                sql = string.Format(@"CALL REMOVE_NODE_MP({0});", nodeForRemove);
            }
            logger.Log(connectionString, sql, "MP_RemoveNode");
            ExecuteQuery(sql, connectionString);
		}

		#endregion
	}
}
